The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.
A database system includes a database server that maintains and provides access to a database. The database system further includes a storage system that stores the database. Typically, to respond to a query, a database server obtains all relevant records from the storage system and processes the records to generate the result set. Traditional storage systems suffer from bandwidth issues that limit scalability and overall database performance.
A storage system of a database system may include a storage server. A storage server provides storage services to the database server, and can be configured to perform some optimizations to improve performance. For example, a database server can offload database processing to the storage server when the storage server is configured to perform such offloaded database operations.
Oracle's Exadata Storage Server includes a SQL engine that can perform predicate evaluation offloaded from the database server. In this case, when a query includes a predicate, the storage server returns records filtered according to the predicate, thereby eliminating unnecessary data transfers. When the selectivity of the predicate is high, the majority of rows can be eliminated at the storage server, resulting in lower data transmission bandwidth and reducing processing at the database server.
As used herein, the term “extrema function” refers to a function that returns one or more extrema values from a set, such as one or more minimum values and/or one or more maximum values of the set. The plural form of the term “extrema” is used herein to refer to a class of functions and the results thereof, including singular functions and/or singular results; thus the usage herein of the plural form “extrema” applies to both plural and singular cases, such as when a particular extrema function returns a single minimum value or a single maximum value.
A query may include an extrema function that is applied to a particular column and a particular set of data that returns one or more extrema values for the particular column in the particular set of data. The set of data may be a table or any portion thereof, such as a particular range and/or partition. The set of data may also be limited by a filter predicate that is included in the query. As used herein, the term “filter predicate” refers to a predicate that is evaluated at the row level.
Due to the nature of extrema functions, approaches for evaluation of an extrema function read each record of the particular set of data. For example, in response to a SQL query with a MIN function on a column of a set of records, the column value for the set of records must be read from storage. The return value for the extrema function can be computed in one pass as the set of records are read. Thus, the query execution time with respect to evaluating the extrema function is largely dictated by I/O latency.
Based on the foregoing, it is desirable to provide optimizations for executing query with an extrema function.